﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace 图书管理系统
{
    public partial class 还书 : Form
    {
        public 还书()
        {
            InitializeComponent();
        }

        private void 还书_Load(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            User user = 用户登录.user;
            String id = user.Id;
            String Txt = "借阅中";
            SqlConnection conn = new SqlConnection();
            string sql = "select Binfo_Table.ISBN,BorrowinfoTable.BookNum,Borrow_time,BookName,Return_time from BorrowinfoTable,Binfo_Table,Borrowingrules_Table where BorrowinfoTable.ReaderID like'" + id + "' and BorrowinfoTable.BorrowState like'" + Txt + "' and BorrowinfoTable.BookNum = Binfo_Table.BookNum group by Binfo_Table.ISBN,BorrowinfoTable.BookNum,Borrow_time,BookName,Return_time";
            SqlCommand cmd = Dao.SqlConnect(sql, conn);
            try
            {
                conn.Open();
                DataTable ds = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                foreach (DataRow dataRow in ds.Rows)
                {
                    int index = dataGridView1.Rows.Add();
                    dataGridView1.Rows[index].Cells[0].Value = dataRow["ISBN"].ToString();
                    dataGridView1.Rows[index].Cells[1].Value = dataRow["BookNum"].ToString();
                    dataGridView1.Rows[index].Cells[2].Value = dataRow["BookName"].ToString();
                    dataGridView1.Rows[index].Cells[3].Value = dataRow["Borrow_time"].ToString();
                    dataGridView1.Rows[index].Cells[4].Value = dataRow["Return_time"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.Columns[e.ColumnIndex].Name == "续借")
            {
                String ns = dataGridView1.CurrentRow.Cells[1].Value.ToString();
                SqlConnection conn12 = new SqlConnection();
                string sql12 = "select ReplyState from Claimrecord_Table where BookNum like'" + ns + "'";
                SqlCommand cmd12 = Dao.SqlConnect(sql12, conn12);
                conn12.Open();
                SqlDataReader kl = cmd12.ExecuteReader();
                String replystate = null;
                while (kl.Read())
                {
                    replystate = kl["ReplyState"].ToString();
                }
                if (replystate == "通过" || replystate == null)
                {
                    User user = 用户登录.user;
                String id = user.Id;
                String isbn = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                String booknum = dataGridView1.CurrentRow.Cells[1].Value.ToString();
                String Txt = "预约中";
                SqlConnection conn = new SqlConnection();
                string sql = "select * from Appointmentrecord_Table where BookNum like'" + booknum + "' and OrderState like'" + Txt + "'";
                SqlCommand cmd = Dao.SqlConnect(sql, conn);
                conn.Open();
                SqlDataReader total = cmd.ExecuteReader();
                int num = 0;
                while (total.Read())
                {
                    num++;
                }

                /*SqlConnection conn4 = new SqlConnection();
                string sql4 = "select count(*) from Binfo_Table where ISBN like'" + isbn + "'";
                SqlCommand cmd4 = Dao.SqlConnect(sql4, conn4);
                conn4.Open();
                SqlDataReader num4 = cmd4.ExecuteReader();
                String tr4 = null;
                while (num4.Read())
                {
                    tr4 = num4[0].ToString();
                }
                int v = Convert.ToInt32(tr4);

                SqlConnection conn5 = new SqlConnection();
                string sql5 = "select count(*) from Appointmentrecord_Table where ISBN like'" + isbn + "' and OrderState like'" + Txt + "'";
                SqlCommand cmd5 = Dao.SqlConnect(sql5, conn5);
                conn5.Open();
                SqlDataReader num5 = cmd5.ExecuteReader();
                String tr5 = null;
                while (num5.Read())
                {
                    tr5 = num5[0].ToString();
                }
                int u = Convert.ToInt32(tr5);*/


                //SqlConnection conn4 = new SqlConnection();
                //String Txt1 = "借阅中";
                //String nt = dataGridView1.CurrentRow.Cells[1].Value.ToString();
                //String sql4 = "select Borrow_time,Return_time from BorrowinfoTable where ReaderID like'" + id + "' and BorrowState like'" + Txt1 + "' and BookNum like'" + nt + "'";
                //SqlCommand cmd4 = Dao.SqlConnect(sql4, conn4);
                //conn4.Open();
                //SqlDataReader idt4 = cmd4.ExecuteReader();
                //String tr4 = null;
                //String trs4 = null;
                //while (idt4.Read())
                //{
                //tr4 = idt4["Borrow_time"].ToString();
                //trs4 = idt4["Return_time"].ToString();
                //}
                //MessageBox.Show(tr4);
                //MessageBox.Show(trs4);
                DateTime bt1 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[3].Value);
                DateTime rt1 = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[4].Value);
                SqlConnection conn1 = new SqlConnection();
                String identy = "select Identy from Readerinfo where ReaderID like '" + id + "'";
                SqlCommand cmd1 = Dao.SqlConnect(identy, conn1);
                conn1.Open();
                SqlDataReader idt = cmd1.ExecuteReader();
                String tr = null;
                while (idt.Read())
                {
                    tr = idt[0].ToString();
                }

                SqlConnection conn2 = new SqlConnection();
                String sql2 = "select RenewDay,BorrowTerm from Borrowingrules_Table where Identy = '" + tr + "'";
                SqlCommand cmd2 = Dao.SqlConnect(sql2, conn2);
                conn2.Open();
                SqlDataReader th2 = cmd2.ExecuteReader();
                String tr2 = null;
                String tr3 = null;
                while (th2.Read())
                {
                    tr2 = th2["RenewDay"].ToString();
                    tr3 = th2["BorrowTerm"].ToString();
                }
                int n = Convert.ToInt32(tr2);
                int n1 = Convert.ToInt32(tr3);
                int totalday = n + n1;
                System.TimeSpan t3 = rt1 - bt1;
                double getDay = t3.TotalDays;


                if(num!=0 || getDay>= totalday)
                {
                    MessageBox.Show("不可续借，请尽快还书！");
                }
                else
                {
                    MessageBox.Show("续借成功！");
                    DateTime returntime = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[4].Value);
                    DateTime returntime1 = returntime.AddDays(+n);
                    dataGridView1.CurrentRow.Cells[4].Value = returntime1;

                   
                    SqlConnection conn3 = new SqlConnection();
                    string sql3 = "update BorrowinfoTable set Return_time = '" + returntime1 + "' where ReaderID like '" + id + "' and BookNum like'" + ns + "'";
                    SqlCommand cmd3 = Dao.SqlConnect(sql3, conn3);
                    conn3.Open();
                    cmd3.ExecuteNonQuery();
                    conn3.Close();
                    //conn4.Close();
                }
                /*conn4.Close();
                conn5.Close();*/
                    conn.Close();
                    conn1.Close();
                    conn2.Close();
                }
                else
                {
                    MessageBox.Show("赔书申请中，暂时不能还书！");
                }
                conn12.Close();
            }

            if (dataGridView1.Columns[e.ColumnIndex].Name == "归还")
            {
                String ns = dataGridView1.CurrentRow.Cells[1].Value.ToString();
                SqlConnection conn12 = new SqlConnection();
                string sql12 = "select ReplyState from Claimrecord_Table where BookNum like'" + ns + "'";
                SqlCommand cmd12 = Dao.SqlConnect(sql12, conn12);
                conn12.Open();
                SqlDataReader kl = cmd12.ExecuteReader();
                String replystate = null;
                while (kl.Read())
                {
                    replystate = kl["ReplyState"].ToString();
                }

                if (replystate == "通过" || replystate == null)
                {

                    /*修改借阅记录表*/
                    String Txt = "已归还";
               User user = 用户登录.user;
               String id = user.Id;
              
               String returntime = DateTime.Now.ToString();
               SqlConnection conn = new SqlConnection();
               string sql = "update BorrowinfoTable set Return_time = '" + returntime + "' , BorrowState = '" + Txt + "' where ReaderID like '" + id + "' and BookNum like'" + ns + "'";
               SqlCommand cmd = Dao.SqlConnect(sql, conn);
               conn.Open();
               cmd.ExecuteNonQuery();

                /*修改图书信息表*/
                String Txt2 = "预约中";
                SqlConnection conn2 = new SqlConnection();
                string sql2 = "select * from Appointmentrecord_Table where BookNum like'" + ns + "' and OrderState like'" + Txt2 + "'";
                SqlCommand cmd2 = Dao.SqlConnect(sql2, conn2);
                conn2.Open();
                SqlDataReader total = cmd2.ExecuteReader();
                int num = 0;
                while (total.Read())
                {
                    num++;
                }

                String Txt3;
                if (num != 0)
                {
                    Txt3 = "不可借";
                }
                else
                {
                    Txt3 = "可借";
                }

                SqlConnection conn3 = new SqlConnection();
                string sql3 = "update Binfo_Table set BookStatus = '" + Txt3 + "' where BookNum like '" + ns + "'";
                SqlCommand cmd3 = Dao.SqlConnect(sql3, conn3);
                conn3.Open();
                cmd3.ExecuteNonQuery();


                /*修改读者信息表*/
                SqlConnection conn4 = new SqlConnection();
                string sql4 = "select Borrow_num from Readerinfo where ReaderID like'" + id + "'";
                SqlCommand cmd4 = Dao.SqlConnect(sql4, conn4);
                conn4.Open();
                SqlDataReader borrownum = cmd4.ExecuteReader();
                String num4 = null;
                while (borrownum.Read())
                {
                    num4 = borrownum["Borrow_num"].ToString();
                }
                int num5 = Convert.ToInt32(num4)-1;

                SqlConnection conn5 = new SqlConnection();
                string sql5 = "update Readerinfo set Borrow_num = '" + num5 + "' where ReaderID like '" + id + "'";
                SqlCommand cmd5 = Dao.SqlConnect(sql5, conn5);
                conn5.Open();
                cmd5.ExecuteNonQuery();


                SqlConnection conn6 = new SqlConnection();
                String sql6 = "select Identy from Readerinfo where ReaderID like '" + id + "'";
                SqlCommand cmd6 = Dao.SqlConnect(sql6, conn6);
                conn6.Open();
                SqlDataReader idt = cmd6.ExecuteReader();
                String tr = null;
                while (idt.Read())
                {
                    tr = idt[0].ToString();
                }

                /*插入罚款表*/
                String Txt9 = "逾期费";
                SqlConnection conn9 = new SqlConnection();
                String sql9 = "select PenaltyRate from Penaltyrules_Table where FineStyle = '" + Txt9 + "'";
                SqlCommand cmd9 = Dao.SqlConnect(sql9, conn9);
                conn9.Open();
                SqlDataReader th9 = cmd9.ExecuteReader();
                String tr9 = null;
                while (th9.Read())
                {
                    tr9 = th9["PenaltyRate"].ToString();
                }
                float f1 = 0;
                float money = 0;
                DateTime ntime = DateTime.Now;
                DateTime rtime = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[4].Value);
                if (DateTime.Compare(ntime, rtime) > 0)
                {
                    f1 = Convert.ToSingle(tr9);

                    System.TimeSpan t3 = ntime - rtime;
                    double getday = Math.Round(t3.TotalDays);
                    if(getday==0)
                    {
                        money = f1;
                    }
                    else
                    {
                        money = (float)(f1 * getday);
                    }
                    MessageBox.Show("你已逾期，请尽快缴费！");
                SqlConnection conn10 = new SqlConnection();
                string sql10 = "insert into Penaltyrecord_Table(FineMoney,Generate_time,FineState,FineStyle,ReaderID,BookNum) values(@FineMoney,@Generate_time,@FineState,@FineStyle,@ReaderID,@BookNum)";
                SqlCommand cmd10 = Dao.SqlConnect(sql10, conn10);
                conn10.Open();
                String sst = DateTime.Now.ToLocalTime().ToString();
                cmd10.Parameters.Add("@FineMoney", SqlDbType.Float).Value = money;
                cmd10.Parameters.Add("@Generate_time", SqlDbType.DateTime).Value = sst;
                cmd10.Parameters.Add("@FineState", SqlDbType.Char, 30).Value = "未缴费";
                cmd10.Parameters.Add("@FineStyle", SqlDbType.Char, 10).Value = "逾期费";
                cmd10.Parameters.Add("@ReaderID", SqlDbType.Char, 15).Value = id;
                cmd10.Parameters.Add("@BookNum", SqlDbType.Char,20).Value = ns;
                int i = cmd10.ExecuteNonQuery();
                conn10.Close();
                }
                


                /*修改预约信息表*/
                SqlConnection conn7 = new SqlConnection();
                String sql7 = "select OrderTerm from Borrowingrules_Table where Identy = '" + tr + "'";
                SqlCommand cmd7 = Dao.SqlConnect(sql7, conn7);
                conn7.Open();
                SqlDataReader th2 = cmd7.ExecuteReader();
                String tr2 = null;
                while (th2.Read())
                {
                    tr2 = th2["OrderTerm"].ToString();
                }
                int n = Convert.ToInt32(tr2);
                DateTime Tiptime = DateTime.Now;
                String Tiptime1 = Tiptime.AddDays(+n).ToString();
                String tips = "请于" + Tiptime1 + "前取书";


                SqlConnection conn11 = new SqlConnection();
                String sql11 = "select  top 1 * from Appointmentrecord_Table where OrderState ='预约中' and BookNum like '" + ns + "' order by Order_time ASC";
                SqlCommand cmd11 = Dao.SqlConnect(sql11, conn11);
                conn11.Open();
                SqlDataReader th11 = cmd11.ExecuteReader();
                String tr11 = null;
                while (th11.Read())
                {
                    tr11 = th11["OrderID"].ToString();
                }


                SqlConnection conn8 = new SqlConnection();
                string sql8 = "update Appointmentrecord_Table set Tips = '" + tips + "' where OrderID like '" + tr11 + "'";
                SqlCommand cmd8 = Dao.SqlConnect(sql8, conn8);
                conn8.Open();
                cmd8.ExecuteNonQuery();



                dataGridView1.Rows.Remove(dataGridView1.CurrentRow);
                MessageBox.Show("还书成功！");
                conn.Close();
                conn2.Close();
                conn3.Close();
                conn4.Close();
                conn5.Close();
                conn6.Close();
                conn7.Close();
                conn8.Close();
                conn9.Close();
                conn11.Close();
                }
                else
                {
                    MessageBox.Show("赔书申请中，暂时不能还书！");
                }
                conn12.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            User user = 用户登录.user;
            this.Hide();
            首页 form = new 首页(user);
            form.Show();
        }
    }
}
